﻿-- Excell - On - Service Database.
USE master
GO

IF EXISTS ( SELECT * 
			FROM dbo.sysdatabases 
			WHERE name = 'ExcellOnService'
		   )
	DROP DATABASE ExcellOnService

GO

CREATE DATABASE ExcellOnService
GO

USE ExcellOnService
GO
CREATE TABLE CategoryNews
(
	CategoryID INT PRIMARY KEY IDENTITY(1,1),
	CategoryName NVARCHAR(100)
)
GO

CREATE TABLE About
(
	AboutID INT PRIMARY KEY IDENTITY(1,1),
	CategoryID INT REFERENCES CategoryNews(CategoryID),
	Title nvarchar(512) NOT NULL,
	[Content] NTEXT,
	PublishedDate DATETIME,
	ModifiedDate DATETIME,
	CheckActive BIT DEFAULT 1 NOT NULL,
	CheckShow BIT DEFAULT 1 NOT NULL
)
GO

CREATE TABLE Departments
(
	DepartmentID INT PRIMARY KEY IDENTITY(1,1),
	DepartmentName NVARCHAR(100),
	Description NTEXT
)
GO

CREATE TABLE Services
(
	ServiceID INT PRIMARY KEY IDENTITY(1,1),
	[Name] NVARCHAR(100),
	ParentID INT REFERENCES Services(ServiceID),
	Price MONEY not null, -- Prire of Service (per day)	
	Description NTEXT
)
GO

CREATE TABLE [Permissions]
(
	PermissionID INT PRIMARY KEY IDENTITY(1,1),
	PermissionName NVARCHAR(100),
	Description NTEXT,
)
GO

CREATE TABLE Roles
(
	RoleID INT primary key IDENTITY(1,1),
	RoleName NVARCHAR(100),
	Description NTEXT,
)
GO

CREATE TABLE RolePermissions
(
	PermissionID INT REFERENCES [Permissions](PermissionID),
	RoleID INT REFERENCES Roles(RoleID)
	PRIMARY KEY(PermissionID,RoleID)
)

GO

CREATE TABLE Employees
(
	EmployeeID INT PRIMARY KEY IDENTITY(1,1),
	username NVARCHAR(20),
	password NVARCHAR(20),
	FullName NVARCHAR(200),
	Email NVARCHAR(128),	
	Address NVARCHAR(200),	
	BirthDate DATETIME,
	Sex BIT DEFAULT 0 NOT NULL,
	Phone NVARCHAR(24),
	Notes NTEXT,
	isLock BIT DEFAULT 0 NOT NULL, -- Tai khoan co bi khoa hay ko?
	isStatus BIT DEFAULT 0 NOT NULL, -- did you deleted?
	DepartmentID INT REFERENCES Departments(DepartmentID) NOT NULL
)
GO

CREATE TABLE RoleEmployees
(
	RoleID INT REFERENCES Roles(RoleID),
	EmployeeID INT REFERENCES Employees(EmployeeID),
	PRIMARY KEY (RoleID,EmployeeID)
)
GO

CREATE TABLE ServiceEmployee
(
	ServiceID INT REFERENCES Services(ServiceID) NOT NULL,
	EmployeeID INT REFERENCES Employees(EmployeeID) NOT NULL,
	PRIMARY KEY (ServiceID, EmployeeID)
)
GO

CREATE TABLE Clients
(
	ClientID INT PRIMARY KEY IDENTITY(1,1),
	Username nvarchar(20) NOT NULL,
	password nvarchar (100) NOT NULL,
	CompanyName NVARCHAR(200),
	Address NVARCHAR(200),
	Phone NVARCHAR(24),
	Email NVARCHAR(100),
	Description NTEXT,
	[Product Description] NTEXT,
	isLock BIT DEFAULT 0 NOT NULL,
	isStatus BIT DEFAULT 0 NOT NULL
)
GO

CREATE TABLE OrderServices
(
	OrderID INT PRIMARY KEY IDENTITY(1,1),
	ClientID INT REFERENCES Clients(ClientID) NOT NULL,
	OrderDate DATETIME,
	--PaymentDate DATETIME,
	Status BIT, -- hoa don co duoc duyet hay ko
)
GO

CREATE TABLE OrderDetails
(
	OrderID INT REFERENCES OrderServices(OrderID) NOT NULL,
	ServiceID INT REFERENCES Services(ServiceID) NOT NULL,
	Price MONEY NOT NULL,
	Quantity INT NOT NULL,
	StartDate DATETIME,
	EndDate DATETIME,
	PRIMARY KEY (OrderID, ServiceID)
)
GO

CREATE TABLE OrderPayment
(
	PaymentID INT PRIMARY KEY IDENTITY(1,1),
	OrderID INT REFERENCES OrderServices(OrderID) NOT NULL,
	Payment money,
	DurationDate DATETIME, -- thoi han thanh toan
	DatePaid DATETIME -- ngay thanh toan
)

GO
INSERT INTO CategoryNews Values('Services News')
INSERT INTO CategoryNews Values('News')
INSERT INTO CategoryNews Values('About')
INSERT INTO CategoryNews Values('Contact Us')


GO

INSERT INTO Departments Values('HR Management','HR Managemen Decription')
INSERT INTO Departments Values('Administration','Administration Decription')
INSERT INTO Departments Values('Service','Service Decription')
INSERT INTO Departments Values('Training','Training Decription')
INSERT INTO Departments Values('Internet Security','It will take care of any technical related issues and problems like PC of an employee is hanged, PC of an employee is not getting started, One of the software application is not running properly, installing and uninstalling software, etc.')
INSERT INTO Departments Values('Auditors','Auditors Decription')

GO

INSERT INTO Services VALUES('In-bound Services',NULL,4500,'The In-bound service is a service in which one can only receive the calls from the customers. These call centers provide 24 hours service to all customers. The primary goal of these call centers are to receive product orders, help customers both technically and non-technically, to find dealer location.')
INSERT INTO Services VALUES('Out-bound Services',NULL,6000,'The Out-bound service is a service in which the employees of Excell-on call the customers for product promotions, for checking with the customer satisfaction on the services they provide, and for the telemarketing.')
INSERT INTO Services VALUES('Tele Marketing Services',NULL,5500,'The Tele Marketing service is a service which is purely for the promotion of marketing or sales of the products and services.')
INSERT INTO Services VALUES('Technical Support',1,4500,'')
INSERT INTO Services VALUES('Customer Service',1,4500,'')

GO

INSERT INTO Clients VALUES('Fptactech','123456','FPT Aptech','51 Le Dai Hanh', '0436363636','fptAptech@fpt.ac.vn',N'Có mặt tại Việt Nam từ năm 1999, với giáo trình Quốc tế được triển khai đồng bộ trên trên thế giới, APTECH là địa chỉ học tập uy tín của các bạn trẻ Việt Nam ham thích máy tính, đam mê khám phá CNTT, trong đó chủ yếu là các bạn sinh viên và các bạn học sinh phổ thông. Đến nay, tập đoàn APTECH đã đào tạo được khoảng 50.000 lập trình viên Quốc tế cho ngành CNTT Việt Nam.',N'Đào tạo học tập',0,0)
INSERT INTO Clients VALUES('vnghanoi','123456','VNG Corporation','Toa nha Trung Yen 1 - Duong Trung Yên I', '04368974562','vnghn@vng.com.vn',N'Thành lập năm 2004, VNG là công ty đi đầu trong lĩnh vực Internet ở Việt Nam. Trẻ trung, nhiệt huyết, năng động, không ngừng tìm tòi và khám phá công nghệ mới, chúng tôi đang chiếm lĩnh thị trường với hơn 20 sản phẩm giải trí, liên kết cộng đồng, và phần mềm được ưa chuộng nhất.',N'Cung câp các dịch vụ internet',0,0)

select * from clients

GO
INSERT INTO OrderServices VALUES (1,'08/15/2012',0)
INSERT INTO OrderServices VALUES (2,'08/10/2012',1)

GO

INSERT INTO OrderDetails VALUES(1,2,6000,5,'08/10/2012','01/25/2013')
INSERT INTO OrderDetails VALUES(1,3,5500,3,'08/10/2012','10/30/2012')
INSERT INTO OrderDetails VALUES(1,4,5500,3,'08/10/2012','09/30/2012')
INSERT INTO OrderDetails VALUES(1,5,5500,3,'08/10/2012','08/30/2012')

INSERT INTO OrderDetails VALUES(2,2,6000,5,'08/10/2012','08/25/2012')
INSERT INTO OrderDetails VALUES(2,3,5500,3,'08/05/2012','08/30/2012')
INSERT INTO OrderDetails VALUES(2,4,5500,3,'08/02/2012','08/30/2012')

GO

INSERT INTO OrderPayment VALUES(1,100000,'08/15/2012',null)
INSERT INTO OrderPayment VALUES(1,100000,'08/20/2012',null)